﻿Public Class PhieuNhapDAO
    Public Shared Function SelectAll() As DataTable
        Dim strSQL As String = "Select ct.IDCTPN as 'Mã CT Phiếu Nhập', sp.TenSp, ct.SoLuongNhap, ct.DonGia, ct.IDMaPN as 'Mã Phiếu Nhập' , ncc.TenNhaCC , pn.NgayNhap From CTPHIEUNHAP ct, PHIEUNHAP pn, NHACUNGCAP ncc, SANPHAM sp where ct.IDMaPN = pn.IDMaPN and ct.IDMaSP = sp.IDMaSP and ct.IDNhaCC = ncc.IDNhaCC "
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function
    Public Shared Function SelectAllCTPN(ByVal iMaPhieuNhap As Integer) As DataTable
        Dim strSQL As String = "Select ct.IDCTPN as 'Mã CT Phiếu Nhập', sp.TenSp, ct.SoLuongNhap, ct.DonGia, ct.IDMaPN as 'Mã Phiếu Nhập' , ncc.TenNhaCC , pn.NgayNhap From CTPHIEUNHAP ct, PHIEUNHAP pn, NHACUNGCAP ncc, SANPHAM sp where ct.IDMaPN = pn.IDMaPN and ct.IDMaSP = sp.IDMaSP and ct.IDNhaCC = ncc.IDNhaCC and pn.IDMaPN = " & iMaPhieuNhap
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function
    'Insert chi tiet phieu nhap vao bang PHIEUNHAP
    Public Shared Function InsertPN(ByVal _pn As PhieuNhapDTO) As Integer
        Dim strSQL As String = "Insert into PHIEUNHAP(NgayNhap) Values(" & _pn.NgayNhap.Date & ")"
        SQLProvider.ExecNoneQuery(strSQL)

        strSQL = "Select Max(IDMaPN) from PHIEUNHAP"
        Dim dt As DataTable = SQLProvider.ExecQuery(strSQL)

        Return dt.Rows(0).Item(0)

    End Function

    'Insert chi tiet phieu nhap vao bang CTPHIEUNHAP
    Public Shared Sub InsertCTPN(ByVal _ctpn As CTPhieuNhapDTO)
        Dim strSQL As String = "Insert into CTPHIEUNHAP(IDMaSP, SoLuongNhap, DonGia,IDMaPN,IDNhaCC) Values(" & _ctpn.MaSanPham & "," & _ctpn.SoLuongNhap & "," & _ctpn.DonGia & ", " & _ctpn.MaPhieuNhap & "," & _ctpn.MaNhaCC & " )"
        SQLProvider.ExecNoneQuery(strSQL)

    End Sub
    'Sua thong tin phieu nhap
    Public Shared Sub UpdatePN(ByVal _pn As PhieuNhapDTO)
        Dim strSQL As String = "Update PHIEUNHAP set NgayNhap = " & _pn.NgayNhap.Date & "where IDMaPN = " & _pn.MaPhieuNhap
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub
    'Sua thong tin chi tiet phieu nhap
    Public Shared Sub UpdateCTPN(ByVal _ctpn As CTPhieuNhapDTO)
        Dim strSQL As String = "Update CTPHIEUNHAP set IDMaSP = " & _ctpn.MaSanPham & "', SoLuongNhap = " & _ctpn.SoLuongNhap & "', DonGia = " & _ctpn.DonGia & "', IDMaPN = " & _ctpn.MaPhieuNhap & "', IDNhaCC = " & _ctpn.MaNhaCC & "where IDMaPN = " & _ctpn.MaCTPhieuNhap
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub
    'Xoa thong tin phieu nhap
    Public Shared Sub DeletePN(ByVal _pn As PhieuNhapDTO)
        Dim strSQL As String = "Delete from PHIEUNHAP where IDMaPN = " & _pn.MaPhieuNhap
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub
    'Xoa thong tin chi tiet phieu nhap
    Public Shared Sub DeleteCTPN(ByVal _ctpn As CTPhieuNhapDTO)
        Dim strSQL As String = "Delete from CTPHIEUNHAP where IDCTPN = " & _ctpn.MaCTPhieuNhap
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    'Public Shared Sub SelectByID(ByVal _pn As PhieuNhapDTO)
    '    Dim pn As New PhieuNhapDTO

    '    Dim strSQL As String
    '    strSQL = "select * from PhieuNhap where IDMaPN = " & _pn.MaPhieuNhap

    '    Dim dt As DataTable

    '    dt = SQLProvider.ExecQuery(strSQL)
    '    pn.MaPhieuNhap = dt.Rows(0).Item(0).ToString()
    '    pn.NgayNhap = (dt.Rows(0).Item(1).ToString())

    'End Sub

    Public Shared Function SelectByID(ByVal iMaPhieuNhap As Integer) As PhieuNhapDTO
        Dim pn As New PhieuNhapDTO

        Dim strSQL As String
        strSQL = "select * from PHIEUNHAP where IDMaPN = " & iMaPhieuNhap

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If
        pn.MaPhieuNhap = dt.Rows(0).Item(0).ToString()
        pn.NgayNhap = (dt.Rows(0).Item(1).ToString())

        Return pn
    End Function

    Public Shared Function TotalValue(ByVal iMaPN As Integer) As Integer

        Dim iTotalValue As Integer = 0
        Dim strSQL As String

        strSQL = "select SUM(SoLuongNhap * DonGia) from CTPHIEUNHAP where IDMaPN = " & iMaPN
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        iTotalValue = dt.Rows(0).Item(0).ToString()
        Return iTotalValue
    End Function

    Public Shared Function SelectAllPN(ByVal iMaPN As Integer) As DataTable
        Dim strSQL As String
        strSQL = "select * from CTPHIEUNHAP where IDMaPN = " & iMaPN

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

End Class
